/* Program name: auto_debt.sas;
* Objective: create an estimate of vehicle-related debt and student loan debt.;
* This code requires the clo variable, which is created in clean_tradeline04.sas, so cannot 
  be run on the raw tradeline files;
* Note: HARDCODED DATES BELOW;
*/

libname in '/data';
libname scorein '/data';
%include "/data/tu_formats.sas";
%include "/data/06macros.sas";

data temp (keep = int teditseq terms hicredit acctype loantypt loantype acctbal ecoa crdtlim cll auto_: mortgage_: dtveri student_: ccard_: cctot_: install_: lcred_: unsec_: secgoods_: lease_: dtclose dtopen paypat mop permid rename=(dtclose=dtclo));
  set in.cleantrade07;

  if clo eq 1 or dispcd in ('IA','INA','FTS','FTB','ETS','ETB','ETI') then coo = 1;
  else coo = 0;

  if dtclose gt 0 or coo eq 1 then cll = 1;
  else if acctype in ('I', 'M') and acctbal le 0 and mop eq 1 then cll = 1;
  else cll = 0;

  if acctbal lt 0 then acctbal = .;

/************************ADDED CODE 5/2019*****************/


/* installment loans */
  if loantypt in('IS') then do;
     loantype = "INSTALL"; 
     install_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     install_hist_ontime = paymop1_24;
     install_hist_3060 = pay3059_24;
     install_hist_6090 = pay6089_24;
     install_hist_90120 = pay90119_24;
     install_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then install_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
	      if acctbal eq 0 then install_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(install);
	end;
     end;
  end;

/* line of credit */
  if loantypt in('LC') then do;
     loantype = "LCRED"; 
     lcred_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lcred_hist_ontime = paymop1_24;
     lcred_hist_3060 = pay3059_24;
     lcred_hist_6090 = pay6089_24;
     lcred_hist_90120 = pay90119_24;
     lcred_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lcred_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
	      if acctbal eq 0 then lcred_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lcred);
	end;
     end;
  end;


/* Unsecured */
  if loantypt in('US') then do;
     loantype = "UNSEC"; 
     unsec_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     unsec_hist_ontime = paymop1_24;
     unsec_hist_3060 = pay3059_24;
     unsec_hist_6090 = pay6089_24;
     unsec_hist_90120 = pay90119_24;
     unsec_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then unsec_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
	      if acctbal eq 0 then unsec_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(unsec);
	end;
     end;
  end;

/* Household goods secured & collateral */
  if loantypt in('SO','SH') then do;
     loantype = "SECGOODS"; 
     secgoods_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     secgoods_hist_ontime = paymop1_24;
     secgoods_hist_3060 = pay3059_24;
     secgoods_hist_6090 = pay6089_24;
     secgoods_hist_90120 = pay90119_24;
     secgoods_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then secgoods_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
	      if acctbal eq 0 then secgoods_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(secgoods);
	end;
     end;
  end;


/* lease */
  if loantypt in('LE') then do;
     loantype = "LEASE"; 
     lease_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline99.sas*/
     lease_hist_ontime = paymop1_24;
     lease_hist_3060 = pay3059_24;
     lease_hist_6090 = pay6089_24;
     lease_hist_90120 = pay90119_24;
     lease_hist_120plus	= pay120up_24;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then lease_clo = 1;
	   if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
	      if acctbal eq 0 then lease_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(lease);
	end;
     end;
  end;


/************************STOP ADDED CODE 5/2019*****************/

  * total auto_loans;
  if loantypt in('AU','AL','AR','AT','CA','MB','MT','RV') then do;
     loantype="AUTO";
     auto_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     auto_hist_ontime = paymop1_30;
     auto_hist_3060 = pay3059_30;
     auto_hist_6090 = pay6089_30;
     auto_hist_90120 = pay90119_30;
     auto_hist_120plus	= pay120up_30;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then auto_clo = 1;
	
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
        if acctbal eq 0 then auto_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(auto);
	end; 
     end;
  end;


  * total student debt;
  if loantypt ='ST' then do;
     loantype = "STUD";
     student_open = 1;

     /*count number of payments current/delinquent in past 24 months for all trades*/
     /*constructed from paypat in 02_clean_tradeline03.sas*/
     student_hist_ontime = paymop1_30;
     student_hist_3060 = pay3059_30;
     student_hist_6090 = pay6089_30;
     student_hist_90120 = pay90119_30;
     student_hist_120plus = pay120up_30;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then student_clo = 1;


     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
        if acctbal eq 0 then student_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(student);
     	end;   
     end;
  end;

  /* total mortgage debt*/
  if loantypt in ('CV', 'FH', 'FI', 'FR', 'HE', 'HI', 'PI', 'RE', 'RL', 'RM', 'RT', 'SM', 'VA', 'VM','UK',' ') and acctype eq 'M' then do;
     loantype = "MORT"; 
     mortgage_open = 1;

     mortgage_hist_ontime = paymop1_30;
     mortgage_hist_3060 = pay3059_30;
     mortgage_hist_6090 = pay6089_30;
     mortgage_hist_90120 = pay90119_30;
     mortgage_hist_120plus = pay120up_30;

     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal le 0) then mortgage_clo = 1;
	

     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 then do;
        if acctbal eq 0 then mortgage_clo = 1; 
        if acctbal gt 0 then do;
	   %recentcount(mortgage);
     	end;
     end;
  end;

  /* credit card debt (secured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC') then do;
     loantype = "CCAR"; 
 
     if ecoa = "I" then do;
     	ccard_indiv_open = 1;
     	ccard_indiv_hist_ontime = paymop1_30;
	    ccard_indiv_hist_3060 = pay3059_30;
     	ccard_indiv_hist_6090 = pay6089_30;
     	ccard_indiv_hist_90120 = pay90119_30;
     	ccard_indiv_hist_120plus = pay120up_30;
     end;

     if ecoa = "C" then do;
     	ccard_joint_open = 1;
	    ccard_joint_hist_ontime = paymop1_30;
	    ccard_joint_hist_3060 = pay3059_30;
     	ccard_joint_hist_6090 = pay6089_30;
     	ccard_joint_hist_90120 = pay90119_30;
     	ccard_joint_hist_120plus = pay120up_30;
     end;

     if ecoa in ("M", "S", "C") then do;
     	ccard_cosign_open = 1;
	    ccard_cosign_hist_ontime = paymop1_30;
	    ccard_cosign_hist_3060 = pay3059_30;
     	ccard_cosign_hist_6090 = pay6089_30;
     	ccard_cosign_hist_90120 = pay90119_30;
     	ccard_cosign_hist_120plus = pay120up_30;
     end;

     if ecoa = "A" then do;
     	ccard_notliab_open = 1;
	    ccard_notliab_hist_ontime = paymop1_30;
	    ccard_notliab_hist_3060 = pay3059_30;
     	ccard_notliab_hist_6090 = pay6089_30;
     	ccard_notliab_hist_90120 = pay90119_30;
     	ccard_notliab_hist_120plus = pay120up_30;
     end;

     if ecoa in ("A", "P") then do;
     	ccard_maybeliab_open = 1;
	    ccard_maybeliab_hist_ontime = paymop1_30;
	    ccard_maybeliab_hist_3060 = pay3059_30;
     	ccard_maybeliab_hist_6090 = pay6089_30;
     	ccard_maybeliab_hist_90120 = pay90119_30;
     	ccard_maybeliab_hist_120plus = pay120up_30;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then ccard_indiv_clo = 1;
	
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa = "I" then do;
     	%recentcount(ccard_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then ccard_joint_clo = 1;
 
    /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa = "C" then do;
 	%recentcount(ccard_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then ccard_cosign_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("M", "S", "C") then do;
        %recentcount(ccard_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then ccard_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("A") then do;
        %recentcount(ccard_notliab);
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then ccard_maybeliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("A", "P") then do;
        %recentcount(ccard_maybeliab); 
     end; 

  end;

  /* credit card total debt (secured + unsecured)*/
  /* We want to include accounts with zero balances so that we can construct an accurate measure of how many
  people have no credit cards*/
  if loantypt in('CC','SC') then do;

     if ecoa = "I" then do;
     	cctot_indiv_open = 1;
     	cctot_indiv_hist_ontime = paymop1_30;
	    cctot_indiv_hist_3060 = pay3059_30;
     	cctot_indiv_hist_6090 = pay6089_30;
     	cctot_indiv_hist_90120 = pay90119_30;
     	cctot_indiv_hist_120plus = pay120up_30;
     end;

     if ecoa = "C" then do;
     	cctot_joint_open = 1;
	    cctot_joint_hist_ontime = paymop1_30;
	    cctot_joint_hist_3060 = pay3059_30;
     	cctot_joint_hist_6090 = pay6089_30;
     	cctot_joint_hist_90120 = pay90119_30;
     	cctot_joint_hist_120plus = pay120up_30;
     end;

     if ecoa in ("M", "S", "C") then do;
     	cctot_cosign_open = 1;
	    cctot_cosign_hist_ontime = paymop1_30;
	    cctot_cosign_hist_3060 = pay3059_30;
     	cctot_cosign_hist_6090 = pay6089_30;
     	cctot_cosign_hist_90120 = pay90119_30;
     	cctot_cosign_hist_120plus = pay120up_30;
     end;

     if ecoa = "A" then do;
     	cctot_notliab_open = 1;
	    cctot_notliab_hist_ontime = paymop1_30;
	    cctot_notliab_hist_3060 = pay3059_30;
     	cctot_notliab_hist_6090 = pay6089_30;
     	cctot_notliab_hist_90120 = pay90119_30;
     	cctot_notliab_hist_120plus = pay120up_30;
     end;

     if ecoa in ("A", "P") then do;
     	cctot_maybeliab_open = 1;
	    cctot_maybeliab_hist_ontime = paymop1_30;
	    cctot_maybeliab_hist_3060 = pay3059_30;
     	cctot_maybeliab_hist_6090 = pay6089_30;
     	cctot_maybeliab_hist_90120 = pay90119_30;
     	cctot_maybeliab_hist_120plus = pay120up_30;
     end;

     * individual account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "I" then cctot_indiv_clo = 1;
	  
     /*open account balance, delinquency info*/
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa = "I" then do;
     	%recentcount(cctot_indiv);
     end;

     * joint account;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa = "C" then cctot_joint_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa = "C" then do;
	%recentcount(cctot_joint);
     end;
    
     * cosigner;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("M", "S", "C") then cctot_cosign_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("M", "S", "C") then do;
        %recentcount(cctot_cosign);
     end; 

     * not liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A") then cctot_notliab_clo = 1;
	
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("A") then do;
        %recentcount(cctot_notliab); 
     end; 

     * maybe liable;
     /*how many loans have been closed between last survey and now*/
     if (cll eq 1 or acctbal lt 0) and ecoa in ("A", "P") then cctot_maybeliab_clo = 1;
	 
     /*open account balance, delinquency info*/ 
     if cll ne 1 and acctbal ge 0 and dtveri ge 200607 and ecoa in ("A", "P") then do;
        %recentcount(cctot_maybeliab);
     end; 
  end;

run;
  

proc sort data = temp;
  by permid;
run;
/*select variables that we want to get sum for collapsed dataset*/
proc sql;
     select name
     into :sumlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_debt' or name ? '_num' or name like '%_ontime' 
     or name like '_lim' or name like '%3060' or name like '%6090' or name like '%90120' 
     or name like '%120plus' or name like '%_worse'
     or name ? '_clo' or name ? '_open');
     quit;
/*select lot variables that we want to get min (choose earliest date)*/
proc sql;
     select name 
     into :minlist separated by ' '
     from dictionary.columns
     where libname = 'WORK' and memname = 'TEMP'
     and (name ? '_lot' or name ? '_f120');
     quit;
/*collapse the dataset*/
 proc means data = temp noprint;
  by permid;
  var install: lcred: unsec: secgoods: lease: auto: trans: student: mortgage: ccard: cctot:;
  output out=rev_install_type07 min(&minlist)= sum(&sumlist)=;
run;

data rev_install_type07;
    set rev_install_type07;
    drop _type_ _freq_;

    ccard_indiv_joint_debt=sum(ccard_indiv_debt,.5*ccard_cosign_debt);
    cctot_indiv_joint_debt=sum(cctot_indiv_debt,.5*cctot_cosign_debt);

ccard_indiv_joint_lim=sum(ccard_indiv_lim,.5*ccard_cosign_lim);
    cctot_indiv_joint_lim=sum(cctot_indiv_lim,.5*cctot_cosign_lim);

run;

proc sort data=rev_install_type07; by permid; run;
proc sort data=temp; by permid; run;


data temp_new;

merge temp (in = a keep= permid teditseq dtopen dtveri dtclo terms hicredit install_clo install_open lcred_clo lcred_open unsec_clo unsec_open secgoods_clo secgoods_open lease_clo lease_open student_clo student_open mortgage_clo mortgage_open auto_clo auto_open loantype student_debt int crdtlim acctbal ecoa
                    rename=(student_debt=student_debt_tl student_clo=student_clo_tl student_open=student_open_tl 
install_clo=install_clo_tl 
install_open = install_open_tl
lcred_clo = lcred_clo_tl
lcred_open = lcred_open_tl 
unsec_clo = unsec_clo_tl 
unsec_open = unsec_open_tl 
secgoods_clo = secgoods_clo_tl
secgoods_open = secgoods_open_tl
lease_clo = lease_clo_tl
lease_open = lease_open_tl 
terms=terms_tl 
hicredit=hicredit_tl 
mortgage_clo=mortgage_clo_tl 
                            mortgage_open=mortgage_open_tl auto_clo=auto_clo_tl auto_open=auto_open_tl crdtlim=crdtlim_tl acctbal=acctbal_tl ecoa=ecoa_tl))

rev_install_type07 (in = b) ;
by permid ;      
  
if a; 
run;




/* pull roll up variables from SCORE dataset */
data roll_up07;
     set scorein.tu2007_june (keep= permid RE28 RE34 AT33 AT28 ON33 MT33 IN33 RE33 AT01 CTAM201TOT G041-G045 G057-G071 
     	 		     G082-G087 CBSA07 CensusBlockGroup07 CensusSuffix07 CensusTract07 
			     CountyFIPSCode07 Latitude07 Longitude07 StateFIPSCode07 ZIPCODE 
			     STATE  
     	 		     G104 G093-G094 G096 S059 S063-S064
			     rename=(STATE=STATEchar_07 ZIPCODE=ZIP_07));
     rename CTAM201TOT=tr_am;
     select (STATEchar_07);
     	    when ('AL') STATE=01;
     	    when ('AK') STATE=02;
     	    when ('AZ') STATE=04;
     	    when ('AR') STATE=05;
     	    when ('CA') STATE=06;
     	    when ('CO') STATE=08;
     	    when ('CT') STATE=09;
     	    when ('DE') STATE=10;
     	    when ('DC') STATE=11;
     	    when ('FL') STATE=12;
     	    when ('GA') STATE=13;
     	    when ('HI') STATE=15;
     	    when ('ID') STATE=16;
     	    when ('IL') STATE=17;
     	    when ('IN') STATE=18;
     	    when ('IA') STATE=19;
     	    when ('KS') STATE=20;
     	    when ('KY') STATE=21;
     	    when ('LA') STATE=22;
     	    when ('ME') STATE=23;
     	    when ('MD') STATE=24;
     	    when ('MA') STATE=25;
     	    when ('MI') STATE=26;
     	    when ('MN') STATE=27;
     	    when ('MS') STATE=28;
     	    when ('MO') STATE=29;
     	    when ('MT') STATE=30;
     	    when ('NE') STATE=31;
     	    when ('NV') STATE=32;
     	    when ('NH') STATE=33;
     	    when ('NJ') STATE=34;
     	    when ('NM') STATE=35;
     	    when ('NY') STATE=36;
     	    when ('NC') STATE=37;
     	    when ('ND') STATE=38;
     	    when ('OH') STATE=39;
     	    when ('OK') STATE=40;
     	    when ('OR') STATE=41;
     	    when ('PA') STATE=42;
     	    when ('PR') STATE=43;
     	    when ('RI') STATE=44;
     	    when ('SC') STATE=45;
     	    when ('SD') STATE=46;
     	    when ('TN') STATE=47;
     	    when ('TX') STATE=48;
     	    when ('UT') STATE=49;
     	    when ('VT') STATE=50;
     	    when ('VA') STATE=51;
     	    when ('WA') STATE=53;
     	    when ('WV') STATE=54;
     	    when ('WI') STATE=55;
     	    when ('WY') STATE=56;
     	    when ('VI') STATE=78;
     	    when ('AS') STATE=60;
     	    when ('GU') STATE=66;
	    when ('')   STATE=.;
	    otherwise STATE=0;
     end;
     drop AT01;
run;
*proc contents data=roll_up07; run;

proc sort data = roll_up07;
  by permid;
run;

data in.roll_trades07(rename=(teditseq=teditseq07));
     merge roll_up07
           temp_new ;  /*merge should be unconditional on if=a or if=b*/
     by permid;
run;


proc print data=in.roll_trades07(obs=30);
     var permid STATE StateFIPSCode07 STATEchar_07; *MT33 mortgage_debt IN33 RE33 ccard_indiv_joint_debt;
run;

proc contents data=in.roll_trades07;
run;
